package org.fastsyncer.parser.framework;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Queue;

import org.apache.commons.lang.StringUtils;
import org.fastsyncer.common.constant.ConnectorConstant;
import org.fastsyncer.common.entity.BaseInfo;
import org.fastsyncer.common.entity.DatabaseConfig;
import org.fastsyncer.common.entity.Mapping;
import org.fastsyncer.common.entity.MappingTask;
import org.fastsyncer.connector.framework.AbstractDatabaseConnector;
import org.fastsyncer.connector.util.DatabaseUtil;
import org.json.JSONException;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

public abstract class AbstractDatabaseParser extends AbstractDatabaseConnector implements Parser {

    private static final Logger logger = LoggerFactory.getLogger(AbstractDatabaseParser.class);

    @Override
    public BaseInfo getBaseInfoConnector(DatabaseConfig config, String queryFilter) {
        return null;
    }

    @Override
    public StringBuilder getPageSQL(DatabaseConfig config, StringBuilder sql) {
        return null;
    }

    protected List<Map<String, Object>> filterFields(List<Map<String, Object>> fileds) {
        // 未防止出现以下重复情况
        //[{name=ID, typeName=VARCHAR, type=12}, 
        // {name=ID, typeName=VARCHAR, type=12}, 
        // {name=PARENT_ID, typeName=VARCHAR, type=12}, 
        // {name=PARENT_ID, typeName=VARCHAR, type=12}, 
        // {name=BASEORG_NAME, typeName=VARCHAR, type=12}]
        // 定义最终目标源字段属性结合
        List<Map<String, Object>> finalFileds = new ArrayList<Map<String, Object>>();
        Map<String, Boolean> f = new HashMap<String, Boolean>();
        int fSize = fileds.size();
        Map<String, Object> obj = null;
        String name = null;
        for (int i = 0; i < fSize; i++) {
            obj = fileds.get(i);
            name = String.valueOf(obj.get("name"));
            // 如果有重复字段跳过
            if (null != f.get(name)) {
                continue;
            }
            // 如果该字段为空,排除该属性同步
            if (StringUtils.isBlank(name)) {
                continue;
            }
            // 标记已出现过该字段
            f.put(name, true);
            finalFileds.add(obj);
        }
        f.clear();
        return finalFileds;
    }

    private String parsePrimaryKey(Mapping mapping) {
        // 判断数据源类型
        DatabaseConfig dbConfig = (DatabaseConfig) mapping.getConfig();
        String pk = dbConfig.getPrimaryKey().toUpperCase();
        if (null == pk) {
            logger.error("The primary key can not be empty!");
            throw new NullPointerException("主键不能为空!");
        }
        return pk;
    }

    @Override
    public boolean insert(MappingTask mappingTask, Map<String, Object> row) throws Exception {
        return this.insert(mappingTask, row, true);
    }

    @Override
    public boolean update(MappingTask mappingTask, Map<String, Object> row) throws Exception {
        return this.update(mappingTask, row, true);
    }

    @Override
    public boolean delete(MappingTask mappingTask, Map<String, Object> row) throws Exception {
        List<Object> args = new ArrayList<Object>();
        // 主键
        String tpk = this.parsePrimaryKey(mappingTask.getTargetMapping());
        // 主键值
        args.add(row.get(tpk));

        // 获取目标源配置
        Mapping targetMapping = mappingTask.getTargetMapping();

        // 执行删除
        JdbcTemplate jdbcTemplate = null;
        int delete = 0;
        try {
            DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
            jdbcTemplate = super.getJdbcTemplate(targetConfig);
            String sql = targetMapping.getExecuteCommond().get(ConnectorConstant.OPERTION_DELETE);
            delete = jdbcTemplate.update(sql, args.toArray());
            logger.info("delete:" + delete);
        } catch (Exception e) {
            logger.error(e.getClass() + " >> " + e.getLocalizedMessage());
        } finally {
            // 释放连接
            super.close(jdbcTemplate);
        }
        if (delete == 0) {
            throw new Exception("Execute delete failed, data does not exist, please check the parameters, or contact the system administrator!");
        }
        return true;
    }

    @Override
    public Integer[] batchInsert(MappingTask mappingTask, List<Map<String, Object>> rows, Integer[] pos, Queue<JSONObject> reasonQuene) throws Exception {
        Mapping targetMapping = mappingTask.getTargetMapping();

        // 去掉目标重复的映射字段
        List<Map<String, Object>> targetFileds = this.filterFields(targetMapping.getFileds());

        // 批量条数
        int size = rows.size();
        BatchPreparedStatementSetter batchSetter = new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                // 2.获取源同步字段sourcePro:[{name=ID, type=4}, {name=NAME, type=12}]
                // 获取每行中的所有字段值，例如 {ID=123, NAME=张三11}
                batchRowsSetter(ps, targetFileds, rows.get(i));
            }

            @Override
            public int getBatchSize() {
                return size;
            }
        };

        // 总数和成功数
        int success = 0;
        // 目标源实例
        DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
        JdbcTemplate targetJdbc = super.getJdbcTemplate(targetConfig);
        try {
            // 执行批量同步
            String sql = targetMapping.getExecuteCommond().get(ConnectorConstant.OPERTION_INSERT);
            int[] batchUpdate = targetJdbc.batchUpdate(sql, batchSetter);
            for (int i = 0; i < size; i++) {
                // 目前在批量同步Oracle时，成功会返回-2
                success += (batchUpdate[i] == 1 || batchUpdate[i] == -2) ? 1 : 0;
            }
        } catch (Exception e) {
            try {
                // 记录错误信息
                JSONObject err = new JSONObject();
                if (pos != null && pos.length == 2) {
                    err.put("startPos", pos[0]);
                    err.put("endPos", pos[1]);
                }
                err.put("cause", e.getCause());
                err.put("message", e.getMessage());
                reasonQuene.offer(err);
            } catch (JSONException e1) {
                logger.error("throw an error message: Converts a JSONObject exception!");
            }
        } finally {
            // 释放连接
            super.close(targetJdbc);
        }
        return new Integer[] { size, success };
    }

    /**
     * 如果插入失败会抛出异常
     * @param mappingTask
     * @param row
     * @param isGracefully 如果数据重复或插入失败的时候执行更新,true:执行更新命令
     * @return
     * @throws Exception
     */
    private boolean insert(MappingTask mappingTask, Map<String, Object> row, final Boolean isGracefully) throws Exception {
        // 1.获取目标源配置
        Mapping targetMapping = mappingTask.getTargetMapping();

        // 2.去掉目标重复的映射字段
        List<Map<String, Object>> targetFileds = this.filterFields(targetMapping.getFileds());

        PreparedStatementSetter setter = new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                // 设置值参数
                int filedSize = targetFileds.size();
                for (int i = 0; i < filedSize; i++) {
                    // 取出字段和对应值
                    Map<String, Object> filed = targetFileds.get(i);
                    String name = (String) filed.get("name");
                    int type = Integer.parseInt(filed.get("type").toString());
                    Object value = row.get(name);
                    DatabaseUtil.preparedStatementSetter(ps, i + 1, type, value);
                }
            }
        };

        // 3.执行新增
        JdbcTemplate jdbcTemplate = null;
        int insert = 0;
        try {
            DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
            jdbcTemplate = super.getJdbcTemplate(targetConfig);
            // 获取新增语句
            String sql = targetMapping.getExecuteCommond().get(ConnectorConstant.OPERTION_INSERT);
            insert = jdbcTemplate.update(sql, setter);
            logger.info("insert:" + insert);
        } catch (DuplicateKeyException e){
            logger.error(e.getClass() + " >> " + e.getLocalizedMessage());
        } catch (Exception e) {
            logger.error(e.getClass() + " >> " + e.getLocalizedMessage());
        } finally {
            // 释放连接
            super.close(jdbcTemplate);
        }
        if (insert == 0) {
            if(isGracefully){
                logger.info("Failed to execute the insert operation, trying to execute the update operation");
                // 尝试执行更新,为防止this.update会执行插入导致死循环,isGracefully 值为false,不允许继续调用插入
                this.update(mappingTask, row, false);
            }else{
                throw new Exception("Execute insert failed, target source has repeated data, please check the parameters, please contact the system administrator!");
            }
        }
        return true;
    }
    
    /**
     * 如果更新失败会抛出异常
     * @param mappingTask
     * @param row
     * @param isGracefully 如果数据不存在或更新失败的时候执行插入,true:执行插入命令
     * @return
     * @throws Exception
     */
    private boolean update(MappingTask mappingTask, Map<String, Object> row, final Boolean isGracefully) throws Exception {
        // 主键
        String tpk = this.parsePrimaryKey(mappingTask.getTargetMapping());
        // 主键值
        Object tPKVal = (String) row.get(tpk);

        // 1.获取目标源配置
        Mapping targetMapping = mappingTask.getTargetMapping();

        // 2.去掉目标重复的映射字段
        List<Map<String, Object>> targetFileds = this.filterFields(targetMapping.getFileds());

        // 数据源主键值
        PreparedStatementSetter setter = new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                // 获取源同步字段sourcePro:[{name=ID, type=4}, {name=NAME, type=12}]
                // 获取每行中的所有字段值，例如 {ID=123, NAME=张三11}

                // 设置值参数
                int tPKType = 12;
                int filedSize = targetFileds.size();
                for (int i = 0; i < filedSize; i++) {
                    // 取出字段和对应值
                    Map<String, Object> filed = targetFileds.get(i);
                    String name = (String) filed.get("name");
                    int type = Integer.parseInt(filed.get("type").toString());
                    // 匹配主键类型
                    tPKType = tpk.equals(name) ? type : tPKType;
                    Object value = row.get(name);
                    DatabaseUtil.preparedStatementSetter(ps, i + 1, type, value);
                }

                // 设置主键参数
                DatabaseUtil.preparedStatementSetter(ps, filedSize + 1, tPKType, tPKVal);
            }
        };

        // 3.执行更新
        JdbcTemplate jdbcTemplate = null;
        int update = 0;
        try {
            DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
            jdbcTemplate = super.getJdbcTemplate(targetConfig);
            // 获取更新语句
            String sql = targetMapping.getExecuteCommond().get(ConnectorConstant.OPERTION_UPDATE);
            update = jdbcTemplate.update(sql, setter);
            logger.info("update:" + update);
        } catch (Exception e) {
            logger.error(e.getClass() + " >> " + e.getLocalizedMessage());
        } finally {
            // 释放连接
            super.close(jdbcTemplate);
        }

        // 如果更新失败
        if (update == 0) {
            if(isGracefully){
              logger.info("Failed to execute the update operation, trying to execute the insert operation");
              // 尝试执行插入,为防止this.insert会执行更新导致死循环,isGracefully 值为false,不允许继续调用更新
              this.insert(mappingTask, row, false);
            }else{
                throw new Exception("Execute update failed, data or does not exist, please check the parameters, or contact the system administrator!");
            }
        }
        return true;
    }
    
}
